package com.lingxu;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

/**
 * @program: ptmh
 * @ClassName TableUtils
 * @description:
 * @author: gxy
 * @create: 2025-07-17 13:37
 * @Version 1.0
 **/
public class TableUtils {

    @Test
    public void buildSql() {
        String path = "C:\\Users\\Green\\Desktop\\交通厅材料\\国道\\1030.xlsx";
        String dbName = "DIMENMODEL";
        List<ReadSheet> sheets = new ArrayList<>();
        try {
            ExcelReader excelReader = EasyExcel.read(new File(path)).build();
            sheets = excelReader.excelExecutor().sheetList();
        } catch (Exception e) {
            e.printStackTrace();
        }
        sheets.forEach(s->{
            NoModelDataListener noModelDataListener = new NoModelDataListener();
            try {
                EasyExcel.read(new File(path), noModelDataListener).headRowNumber(0).sheet(s.getSheetNo()).doRead();
            } catch (Exception e) {
                e.printStackTrace();
            }
            List<YdDataDetail> res = noModelDataListener.getDataList();
            s.getSheetName();
            String tableName = dbName+".API_"+s.getSheetName();
            String viewName = dbName+".API_VIEW_"+s.getSheetName();
            System.out.println("DROP TABLE IF EXISTS "+tableName+";\n");
            StringBuilder vb = new StringBuilder("CREATE OR REPLACE VIEW "+viewName+" AS select \n");
            StringBuilder vbc = new StringBuilder();
            StringBuilder sb = new StringBuilder("CREATE TABLE "+tableName+" ( \n");
            StringBuilder sbc = new StringBuilder();
            for (int i=1;i<res.size();i++) {
                YdDataDetail detail = res.get(i);
                if(detail.getValue1().toLowerCase(Locale.ROOT).equals("clob")) {
                    sb.append(detail.getValue0() + " text, \n");
                } else if(detail.getValue1().toLowerCase(Locale.ROOT).equals("number")) {
                    sb.append(detail.getValue0() + " number(18,2), \n");
                } else {
                    sb.append(detail.getValue0() + " " + (detail.getValue1().toLowerCase(Locale.ROOT).equals("clob") ? "text" : detail.getValue1()) + (
                            StringUtils.isNotBlank(detail.getValue2()) ? "(" + detail.getValue2() + ")" : ""
                    ) + ", \n");
                }
//                if(!detail.getValue1().toLowerCase(Locale.ROOT).equals("clob")) {
                    vb.append(detail.getValue0()+", \n");
                    vbc.append("COMMENT ON COLUMN "+viewName+"."+detail.getValue0()+" IS '"+(StringUtils.isNotBlank(detail.getValue3())?detail.getValue3():"")+"';\n");
//                }
                sbc.append("COMMENT ON COLUMN "+tableName+"."+detail.getValue0()+" IS '"+(StringUtils.isNotBlank(detail.getValue3())?detail.getValue3():"")+"';\n");
            }
            sb.deleteCharAt(sb.length()-3);
            sb.append(");\n");
            vb.deleteCharAt(vb.length()-3);
            vb.append(" from "+tableName+";\n");
            System.out.println(sb.toString());
            System.out.println("COMMENT ON TABLE "+tableName+" IS '"+res.get(0).getValue4()+"';\n");
            System.out.println(sbc.toString());
            System.out.println(vb.toString());
            System.out.println(vbc.toString());
            System.out.println("COMMENT ON VIEW "+viewName+" IS '视图"+res.get(0).getValue4()+"';\n");
        });

    }

}
